﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaintenanceConsistencyCheckAll]
#-- Purpose:		Calls spMaintenanceConsistencyCheck for all databases.
#--	Last Update:	01/25/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Called By:		Scheduled Job
#-- Required XPs:	None
#-- Readings:		http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaintenanceConsistencyCheckAll]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@db_name sysname

--- Execute the command
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT		db.name
    FROM		master.sys.databases db
    JOIN		master.sys.master_files mf ON db.database_id = mf.database_id
				AND mf.type = 0
	WHERE		db.name NOT IN (SELECT database_name FROM dbo.vwDatabase_DoNotMaintain)
    GROUP BY	db.name
    ORDER BY	SUM(mf.size)
OPEN crs
FETCH NEXT FROM crs INTO @db_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	EXEC dbo.spMaintenanceConsistencyCheck @database_name = @db_name

	FETCH NEXT FROM crs INTO @db_name
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF
